<?php
/**
 *    客服导出统计数据
 *
 *    @author    yanghj
 *    @date    2015-01-31
 */

class Export_dataApp extends BackendApp
{
    /**
     *   导出数据列表
     */
    function index()
    {
        $this->display('export_data.index.html');
    }

    /* 导出三级分类商品 */
    function category_goods()
    {
        $goods_mod = &m('goods');
        
        $sql = "SELECT g.goods_id,g.cate_id_3,c.cate_name,s.store_id,s.store_name
    FROM  ".DB_PREFIX."goods g
    LEFT JOIN ".DB_PREFIX."gcategory c ON g.cate_id_3=c.cate_id
    LEFT JOIN ".DB_PREFIX."store s ON s.store_id=g.store_id
    WHERE 1 ORDER BY g.store_id ASC";

        $data1 = $goods_mod->getAll($sql);

        $store = array();
        $gcategory = array();
        foreach($data1 as $key => $val)
        {
            $gcategory[$val['cate_id_3']]=$val['cate_name'];
            $store[$val['store_id']]=$val['store_name'];
            $data[$val['cate_id_3']][$val['store_id']][$val['goods_id']]=$val['goods_id'];
        }
        $heads['cate_name']['name'] = '分类名称';
        $heads['sum']['name'] = '商品数量';
        $total['cate_name'] = '合计';
        foreach($gcategory as $cate_id => $cate_name)
        {
            $temp[$cate_id]['cate_name'] = $cate_name;
            
            foreach($store as $store_id => $store_name)
            {
                $temp[$cate_id]['sum'] += count($data[$cate_id][$store_id]);
                $temp[$cate_id][$store_id] = count($data[$cate_id][$store_id]) ;
                $heads[$store_id]['name'] = $store_id.$store_name;

                $total[$store_id] += count($data[$cate_id][$store_id]);              
            }
            $total['sum'] += $temp[$cate_id]['sum'];
        }
        $temp[] = $total;
        export_excel('三级分类商品', $heads, $temp);
        exit;
        
    }

    /* 导出全部产品 */
    function all_goods()
    {
        $goodsspec_mod = &m('goodsspec');

        $sql = "SELECT s.store_id,t.store_name,g.goods_id,s.goods_name,g.sku as goods_model,g.spec_1,g.spec_2 FROM ".DB_PREFIX."goods_spec g
LEFT JOIN ".DB_PREFIX."goods s ON g.goods_id=s.goods_id
LEFT JOIN ".DB_PREFIX."store t ON s.store_id=t.store_id
WHERE 1 ORDER BY s.store_id ASC, s.goods_id ASC";

        $data = $goodsspec_mod->getAll($sql);
        $heads = $this->_get_all_goods_heads();
        // require(ROOT_PATH."/includes/PHPExcel/export_excel.php");
        //createExcel('全部商品', $heads, $data);
        export_excel('全部商品', $heads, $data);
        exit;

    }

    /* 导出按规格报价产品 */
    function spec_goods()
    {
        $goodsspec_mod = &m('goodsspec');

        $sql = "SELECT s.store_id,t.store_name,g.goods_id,s.goods_name,g.sku as goods_model,g.spec_1,g.spec_2,g.base_price as goods_clearing_price,g.price as goods_price,g.market_price FROM ".DB_PREFIX."goods_spec g
LEFT JOIN ".DB_PREFIX."goods s ON g.goods_id=s.goods_id
LEFT JOIN ".DB_PREFIX."store t ON s.store_id=t.store_id
WHERE  s.price_type =2 ORDER BY s.store_id ASC, s.goods_id asc";

        $data = $goodsspec_mod->getAll($sql);
        $heads = $this->_get_spec_goods_heads();
       // require(ROOT_PATH."/includes/PHPExcel/export_excel.php");
        //createExcel('按规格报价商品', $heads, $data);
        export_excel('按规格报价商品', $heads, $data);
        exit;

    }

    /* 导出按数量报价商品 */
    function volume_goods()
    {
        $goodsspec_mod = &m('goodsspec');

        $sql = "SELECT s.store_id,st.store_name,g.goods_id,s.goods_name,t.sku as goods_model,g.volume_number,s.clearing_price as goods_clearing_price,g.volume_price as goods_price,g.market_price FROM wl_volume_price g
    LEFT JOIN ".DB_PREFIX."goods s ON g.goods_id=s.goods_id
    LEFT JOIN (SELECT sku,goods_id FROM ".DB_PREFIX."goods_spec group by goods_id) t on g.goods_id = t.goods_id
    LEFT JOIN ".DB_PREFIX."store st ON s.store_id=st.store_id
    WHERE  s.price_type =1
    ORDER BY s.store_id ASC, s.goods_id asc, g.volume_number asc";

        $data = $goodsspec_mod->getAll($sql);
        $heads = $this->_get_volume_goods_heads();
        //require(ROOT_PATH."/includes/PHPExcel/export_excel.php");
        //createExcel('按数量报价商品', $heads, $data);
        export_excel('按数量报价商品', $heads, $data);
        exit;

    }

    private  function _get_all_goods_heads()
    {
        return array(
            'store_id' => array('name' => "店铺ID" ),
            'store_name' => array('name' => "店铺名称" ),
            'goods_id' => array('name' => "商品ID"),
            'goods_name' => array('name' => "商品名称"),
            'goods_model' => array('name' => "商品型号"),
            'spec_1' => array('name' => "规格1"),
            'spec_2' => array('name' => "规格2"),
        );
    }

    private  function _get_spec_goods_heads()
    {
        return array(
            'store_id' => array('name' => "店铺ID" ),
            'store_name' => array('name' => "店铺名称" ),
            'goods_id' => array('name' => "商品ID"),
            'goods_name' => array('name' => "商品名称"),
            'goods_model' => array('name' => "商品型号"),
            'spec_1' => array('name' => "规格1"),
            'spec_2' => array('name' => "规格2"),
            'goods_clearing_price' => array('name' => "数据库商品底价"),
            'goods_price' => array('name' => "数据库商品价格"),
            'table_goods_clearing_price' => array('name' => "清单商品底价"),
            'table_goods_price' => array('name' => "清单商品价格"),
            'remark' => array('name' => "备注"),
        );
    }

    private  function _get_volume_goods_heads()
    {
        return array(
            'store_id' => array('name' => "店铺ID" ),
            'store_name' => array('name' => "店铺名称" ),
            'goods_id' => array('name' => "商品ID"),
            'goods_name' => array('name' => "商品名称"),
            'goods_model' => array('name' => "商品型号"),
            'volume_number' => array('name' => "价格区间"),
            'goods_clearing_price' => array('name' => "数据库商品底价"),
            'goods_price' => array('name' => "数据库商品价格"),
            'table_goods_clearing_price' => array('name' => "清单商品底价"),
            'table_goods_price' => array('name' => "清单商品价格"),
            'remark' => array('name' => "备注"),
        );
    }


}

?>
